﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QQTools.Data.Models;

namespace QQTools.Data.Repositories.InviteGroup
{
    public class InviteGroupQQRepository
    {
        private readonly string _connectionString;

        public InviteGroupQQRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task<List<InviteGroupQQModel>> GetAllAsync()
        {
            var inviteQQs = new List<InviteGroupQQModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM invite_qq", connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            inviteQQs.Add(MapTo(reader));
                        }
                    }
                }
            }

            return inviteQQs;
        }

        public async Task<InviteGroupQQModel> GetByIdAsync(int id)
        {
            InviteGroupQQModel inviteQQ = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM invite_qq WHERE id = @Id", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            inviteQQ = MapTo(reader);
                        }
                    }
                }
            }

            return inviteQQ;
        }

        public async Task<List<InviteGroupQQModel>> GetByQQAsync(string qq)
        {
            var inviteQQs = new List<InviteGroupQQModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM invite_qq WHERE qq = @QQ", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            inviteQQs.Add(MapTo(reader));
                        }
                    }
                }
            }

            return inviteQQs;
        }

        public async Task AddAsync(InviteGroupQQModel inviteQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    INSERT INTO invite_qq (qq, invite_number, invite_success_count)
                    VALUES (@QQ, @InviteNumber, @invite_success_count);", connection))
                {
                    command.Parameters.AddWithValue("@QQ", inviteQQ.QQ);
                    command.Parameters.AddWithValue("@InviteNumber", inviteQQ.InviteNumber);
                    command.Parameters.AddWithValue("@invite_success_count", inviteQQ.InviteSuccessCount);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task UpdateAsync(InviteGroupQQModel inviteQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE invite_qq
                    SET qq = @QQ, invite_number = @InviteNumber, invite_success_count = @InviteSuccessCount, update_time = CURRENT_TIMESTAMP
                    WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", inviteQQ.Id);
                    command.Parameters.AddWithValue("@QQ", inviteQQ.QQ);
                    command.Parameters.AddWithValue("@InviteNumber", inviteQQ.InviteNumber);
                    command.Parameters.AddWithValue("@InviteSuccessCount", inviteQQ.InviteSuccessCount);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task DeleteAsync(int id)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM invite_qq WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task ClearTableAsync()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM invite_qq;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task AddOrUpdateAsync(InviteGroupQQModel inviteQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                // 检查 qq 是否已经存在
                using (var checkCommand = new SQLiteCommand("SELECT id FROM invite_qq WHERE qq = @QQ", connection))
                {
                    checkCommand.Parameters.AddWithValue("@QQ", inviteQQ.QQ);
                    var existingId = await checkCommand.ExecuteScalarAsync();

                    if (existingId != null)
                    {
                        // 如果存在，则更新数据
                        int id = Convert.ToInt32(existingId);
                        inviteQQ.Id = id;
                        await UpdateAsync(inviteQQ);
                    }
                    else
                    {
                        // 如果不存在，则插入新数据
                        await AddAsync(inviteQQ);
                    }
                }
            }
        }


        /// <summary>
        /// 增加 qq 的邀请次数
        /// </summary>
        /// <param name="qq"></param>
        /// <returns></returns>
        public async Task IncrementInviteNumberAsync(string qq)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                // 检查 qq 是否已经存在
                using (var checkCommand = new SQLiteCommand("SELECT id, invite_number FROM invite_qq WHERE qq = @QQ", connection))
                {
                    checkCommand.Parameters.AddWithValue("@QQ", qq);
                    using (var reader = await checkCommand.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            // 如果存在，则更新 invite_number
                            int id = reader.GetInt32(reader.GetOrdinal("id"));
                            int currentInviteNumber = reader.GetInt32(reader.GetOrdinal("invite_number"));
                            int newInviteNumber = currentInviteNumber + 1;

                            using (var updateCommand = new SQLiteCommand(@"
                                UPDATE invite_qq
                                SET invite_number = @InviteNumber, update_time = CURRENT_TIMESTAMP
                                WHERE id = @Id;", connection))
                            {
                                updateCommand.Parameters.AddWithValue("@Id", id);
                                updateCommand.Parameters.AddWithValue("@InviteNumber", newInviteNumber);

                                await updateCommand.ExecuteNonQueryAsync();
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 增加 qq 的邀请成功次数
        /// </summary>
        /// <param name="qq"></param>
        /// <returns></returns>
        public async Task IncrementInviteSuccessCountAsync(string qq)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                // 检查 qq 是否已经存在
                using (var checkCommand = new SQLiteCommand("SELECT id, invite_success_count FROM invite_qq WHERE qq = @QQ", connection))
                {
                    checkCommand.Parameters.AddWithValue("@QQ", qq);
                    using (var reader = await checkCommand.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            // 如果存在，则更新 invite_success_count
                            int id = reader.GetInt32(reader.GetOrdinal("id"));
                            int currentInviteSuccessCount = reader.IsDBNull(reader.GetOrdinal("invite_success_count")) ? 0 : reader.GetInt32(reader.GetOrdinal("invite_success_count"));
                            int newInviteSuccessCount = currentInviteSuccessCount + 1;

                            using (var updateCommand = new SQLiteCommand(@"
                                UPDATE invite_qq
                                SET invite_success_count = @InviteSuccessCount, update_time = CURRENT_TIMESTAMP
                                WHERE id = @Id;", connection))
                            {
                                updateCommand.Parameters.AddWithValue("@Id", id);
                                updateCommand.Parameters.AddWithValue("@InviteSuccessCount", newInviteSuccessCount);

                                await updateCommand.ExecuteNonQueryAsync();
                            }
                        }
                    }
                }
            }
        }


        public async Task UpdateInviteSuccessZero()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE invite_qq
                    SET invite_success_count = 0, update_time = CURRENT_TIMESTAMP
                    WHERE 1 = 1;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }
        /// <summary>
        /// 将 SqlDataReader 映射到 InviteGroupQQModel 对象
        /// </summary>
        private InviteGroupQQModel MapTo(System.Data.Common.DbDataReader reader)
        {
            return new InviteGroupQQModel
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                QQ = reader.IsDBNull(reader.GetOrdinal("qq")) ? null : reader.GetString(reader.GetOrdinal("qq")),
                InviteNumber = reader.IsDBNull(reader.GetOrdinal("invite_number")) ? 0 : reader.GetInt32(reader.GetOrdinal("invite_number")),
                InviteSuccessCount = reader.IsDBNull(reader.GetOrdinal("invite_success_count")) ? 0 : reader.GetInt32(reader.GetOrdinal("invite_success_count")),
                CreateTime = reader.IsDBNull(reader.GetOrdinal("create_time")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("create_time")),
                UpdateTime = reader.IsDBNull(reader.GetOrdinal("update_time")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("update_time"))
            };
        }
    }
}
